package com.supermap.dataservice.monitor.dao;

import com.supermap.dubbo.api.monitor.entity.Active;
import com.supermap.dubbo.api.monitor.entity.AgrCrop;
import com.supermap.dubbo.api.monitor.entity.SerObj;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author lxs
 * @Title:监控平台数据访问层
 * @Description:
 * @date 2018/7/11  11:16
 */
@Mapper
public interface MonitorDao {
    //当年月各类作物农情上传的数量
    @Select("select t1.id,t2.type,MONTH(t1.createtime) as mon,COUNT(t2.type) as nums from t_agrinfo t1 \n" +
            " INNER JOIN t_crop_type t2 on t1.type=t2.cropid where t1.areacode like #{areacode}  and t1.del_flg=0\n" +
            " and YEAR(t1.createtime)=#{year} GROUP BY t2.type,MONTH(t1.createtime) ORDER BY mon")
    List<AgrCrop> getAgrCropsByAreacode(@Param("areacode")String areacode, @Param("year")Integer year);

    @Select("select type from t_crop_type GROUP BY type")
    List<String> getCropsTypesList();

    //各类服务对象总数量
    @Select("select t3.name,COUNT(t3.name) as nums from t_user t1 INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id  where t1.areaCode like #{areaCode}  GROUP BY t3.name")
    List<SerObj> getSerObjListByAreacode(@Param("areaCode") String areaCode);

    @Select("select id,name,code from t_role")
    List<SerObj> getRoleList();
    //各类服务对象当月新增总数量
    @Select("select t3.name,COUNT(t3.name) as nums from t_user t1 INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id  where t1.areaCode like #{areaCode} AND YEAR(t1.createtime)=#{year} and MONTH(t1.createtime)=#{mon} GROUP BY t3.name")
    List<SerObj> getSerObjListByAreacodeAndYearMonth(@Param("areaCode") String areaCode, @Param("year")Integer year, @Param("mon")Integer mon);
    //专家活跃度(含咨询，灾情等)
    @Select("select t1.id,t1.username,t1.mobile,t1.HeadImage,t5.areaname,t1.Introduction,COUNT(t1.username) as nums from t_user t1 " +
            "INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id " +
            "inner join (SELECT * FROM t_advcomment UNION all SELECT * FROM t_agrinfocomment )t4 on t1.id=t4.userId " +
            "inner join t_area t5 on t1.areaCode=t5.areacode " +
            "where t3.code='EXPERT' and t1.areaCode like #{areaCode} group by t1.username order by  nums desc")
    List<Active> getActiveListByareaCode(@Param("areaCode") String areaCode);

    @Select("select t4.id,t1.id as commentid,t1.username,t1.mobile,t1.HeadImage,t5.areaname,t1.Introduction,t4.comment,t4.parentId,t4.commentTime  from t_user t1 " +
            "INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            "INNER JOIN t_role t3 on t2.roleid=t3.id " +
            "inner join (SELECT * FROM t_advcomment UNION all SELECT * FROM t_agrinfocomment )t4 on t1.id=t4.userId  " +
            "inner join t_area t5 on t1.areaCode=t5.areacode   " +
            "where t1.id=#{userId} GROUP BY t4.parentId order by t4.commentTime desc")
    List<Active> getTreeList(@Param("userId") String userId);

    @Select("select t4.id,t1.id as commentid,t1.username,t1.mobile,t1.HeadImage,t1.Introduction,t4.comment,t4.parentId,t4.commentTime  from t_user t1 " +
            " INNER JOIN t_user_role t2  on t1.id=t2.userid " +
            " INNER JOIN t_role t3 on t2.roleid=t3.id " +
            " inner join (SELECT * FROM t_advcomment UNION all SELECT * FROM t_agrinfocomment )t4 on t1.id=t4.userId " +
            "where t4.id=#{id} ")
    Active getPComment(@Param("id") String id);

    /**
     * 当年上传每月的灾情统计
     * @param year
     * @param areaCode
     * @return
     */
    @Select("select id,MONTH(createtime)as mon,COUNT(1) as nums from t_disaster \n" +
            "where areacode like #{areaCode} and YEAR(createtime)=#{year} GROUP BY MONTH(createtime) ORDER BY mon")
    List<AgrCrop> getDisList(@Param("year")Integer year,@Param("areaCode") String areaCode);

    /**
     * 当年上传每月的咨询统计
     * @param year
     * @param areaCode
     * @return
     */
    @Select("select id,MONTH(uploadTime)as mon,COUNT(1) as nums from t_advisoryinfo \n" +
            "where areacode like #{areaCode} and YEAR(uploadTime)=#{year} GROUP BY MONTH(uploadTime) ORDER BY mon")
    List<AgrCrop> getAdvList(@Param("year")Integer year,@Param("areaCode") String areaCode);

    /**
     * 当年上传每月农情统计
     * @param year
     * @param areaCode
     * @return
     */
    @Select("select id,MONTH(createtime)as mon,COUNT(1) as nums from t_agrinfo \n" +
            "where areacode like #{areaCode} and YEAR(createtime)=#{year} GROUP BY MONTH(createtime) ORDER BY mon")
    List<AgrCrop> getAgrList(@Param("year")Integer year,@Param("areaCode") String areaCode);
}
